package be.vdab.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import be.vdab.entities.Saus;

public class SauzenDAO extends AbstractDAO {
	private static final String FIND_ALL_SQL = 
			 			"select sauzen.nummer, sauzen.naam as sausnaam," +
					    " ingredienten.naam as ingredientnaam" +
					    " from sauzen left join sauzeningredienten" +
					    " on sauzen.nummer=sauzeningredienten.sausnummer" +
					    " left join ingredienten " +
					    " on sauzeningredienten.ingredientnummer=ingredienten.nummer" +
					    " order by sauzen.naam";
	private static final String FIND_BY_INGREDIENT_SQL = 
						"select sauzen.nummer, sauzen.naam" +
					    " from sauzen inner join sauzeningredienten " + 
					    " on sauzen.nummer=sauzeningredienten.sausnummer" +
					    " inner join ingredienten " +
					    "on sauzeningredienten.ingredientnummer=ingredienten.nummer" +
					    " where ingredienten.naam = ?" + " order by sauzen.naam";
	private static final String DELETE_TABLE_SQL = "delete from sauzen where nummer = ?";
	
	  
	public Collection<Saus> findAll() {
		try (Connection connection = getConnection();
				Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,  ResultSet.CONCUR_READ_ONLY);
				ResultSet resultSet = statement.executeQuery(FIND_ALL_SQL)) {
			List<Saus> sauzen = new ArrayList<>();
			while (resultSet.next()) {
				sauzen.add(resultSetRijNaarSausMetIngredienten(resultSet));
			}
			return sauzen;
		} catch (SQLException ex) {
			throw new DAOException("Kan geen sauzen in database vinden", ex);
		}
	}

	private Saus resultSetRijNaarSausMetIngredienten(ResultSet resultSet1) throws SQLException {
		long nummer = resultSet1.getLong("nummer");
	    List<String> ingredienten = new ArrayList<String>();
	    String ingredient = resultSet1.getString("ingredientnaam");
	    if (!resultSet1.wasNull()) {
	      ingredienten.add(ingredient);
	    }
	    Saus saus = new Saus(nummer, resultSet1.getString("sausnaam"), ingredienten);
	    while (resultSet1.next() && resultSet1.getLong("nummer") == nummer) {
	       saus.getIngredienten().add(resultSet1.getString("ingredientnaam"));
	    }
	    return saus;
	}

	public List<Saus> findByIngredient(String ingredient) {
		try (Connection connection2 = getConnection();
				PreparedStatement statement2 = connection2.prepareStatement(FIND_BY_INGREDIENT_SQL)) {
			statement2.setString(1, ingredient);
			List<Saus> sauzen = new ArrayList<>();
			try (ResultSet resultSet = statement2.executeQuery()) {
		        while (resultSet.next()) {
		          sauzen.add(resultSetRijNaarSausZonderIngredienten(resultSet));
		        }
		        resultSet.previous();
		        return sauzen;
			}
	    } catch (SQLException ex) {
				throw new DAOException("Kan geen ingredienten in database vinden",	ex);
		}
	}
	
	private Saus resultSetRijNaarSausZonderIngredienten(ResultSet resultSet) throws SQLException {
		    return new Saus(resultSet.getLong("nummer"), resultSet.getString("naam"), new ArrayList<String>());
		  }

	public void delete(long nummer) {
		try (Connection connection3 = getConnection();
				PreparedStatement statement3 = connection3.prepareStatement(DELETE_TABLE_SQL)) {
			 statement3.setLong(1, nummer);
		     statement3.executeUpdate();
	    } catch (SQLException ex) {
	        throw new DAOException("Kan saus " + nummer + " niet verwijderen uit database", ex);
	    }
	}

}